package com.campus_information_portal.dao;

import com.campus_information_portal.entity.MarkCondition;
import com.campus_information_portal.entity.MarkCount;

/**
 * @author 20155884 王晓阳
 * @className : MarkSqlProvider
 * @Description 课程号,课程名称,课程类型,学生id,学期id任两个组合进行查询--成绩管理页面
 * @create 2018/05/07 19:35
 **/
public class MarkSqlProvider {
    public String selectMark(MarkCondition markCondition) {
        //StringBuffer sql = new StringBuffer("select * from mark,course,term where 1=1 and mark.course_id=course.course_id and mark.term_id=term.term_id");
        StringBuffer sql = new StringBuffer("select course.*,term.*,mark.*,student.stu_info_id,student.stu_num,student.stu_name " +
                                            "from mark,course,term,student where 1=1 and mark.course_id=course.course_id and mark.term_id=term.term_id AND student.stu_info_id=mark.stu_info_id");
        //课程号---
        if (markCondition.getCourseId() != null) {
            //  sql.append(" and course_id=#{courseId}");
            sql.append(" and mark.course_id=#{courseId}");
            System.out.println("-MarkSqlProvider-course_id--"+markCondition.getCourseId());
        }
        //课程名称
        if (markCondition.getCourseName() != null&&!("".equals(markCondition.getCourseName()))) {
            sql.append(" and course.course_name=#{courseName}");
            System.out.println("-MarkSqlProvider-getCourseNamexdh--"+markCondition.getCourseName());
        }
        //课程类型
        if (markCondition.getCourseType()!=null&&!("".equals(markCondition.getCourseType()))) {
            sql.append(" and course.course_type=#{courseType}");
            System.out.println("-MarkSqlProvider-course_type666--");
        }
        //学生id--
       if (markCondition.getStuInfoId() != null) {
            sql.append(" and mark.stu_info_id=#{stuInfoId}  ");
            System.out.println("-MarkSqlProvider-stu_info_id--");
        }
       /* //学期id
        if (markCondition.getTermId() != null) {
            sql.append(" and term_id=#{termId}");
            System.out.println("-MarkSqlProvider-termId--");
        }*/
        if (markCondition.getTermYear() != null&&!("".equals(markCondition.getTermYear()))) {
            sql.append(" and term_year=#{termYear}");
            System.out.println("-MarkSqlProvider-termYear--");
        }
        if (markCondition.getTermNum() != null&&!("".equals(markCondition.getTermNum()))) {
            sql.append(" and term_num=#{termNum}");
            System.out.println("-MarkSqlProvider-termNum--");
        }
        System.out.println("sql----" + sql.toString());
        return sql.toString();
    }
    /**
     * @Description 查询不及格成绩按学科查，按学号查，按学期查
     * @author 20155884 王晓阳
     * @methodName selectFallGrade
     * @param markCondition
     * @returnType String
     * @create 2018/5/21 20:31
     **/
    public String selectFallGrade(MarkCondition markCondition){
        //StringBuffer sql = new StringBuffer("select * from mark,course,term where 1=1 and mark.course_id=course.course_id and mark.term_id=term.term_id and mark.mark<60 ");
        StringBuffer sql = new StringBuffer("select course.*,term.*,mark.*,student.stu_info_id,student.stu_num,student.stu_name " +
                " from mark,course,term,student where 1=1 and mark.course_id=course.course_id and mark.term_id=term.term_id and mark.mark<60 AND student.stu_info_id=mark.stu_info_id");

        //课程号---

        if (markCondition.getCourseId() != null) {
            //  sql.append(" and course_id=#{courseId}");
            sql.append(" and mark.course_id=#{courseId}");
            System.out.println("MarkSqlProvider-selectFallGrade-course_id--"+markCondition.getCourseId());
        }
        //课程名称
        if (markCondition.getCourseName() != null&&!("".equals(markCondition.getCourseName()))) {
            sql.append(" and course.course_name=#{courseName}");
            System.out.println("MarkSqlProvider-selectFallGrade-getCourseName--"+markCondition.getCourseName());
        }
        //课程类型
        if (markCondition.getCourseType()!=null&&!("".equals(markCondition.getCourseType()))) {
            sql.append(" and course.course_type=#{courseType}");
            System.out.println("MarkSqlProvider-selectFallGrade-course_type--");
        }
        //学生id
        if (markCondition.getStuInfoId()!= null) {
            sql.append(" and mark.stu_info_id=#{stuInfoId}  ");
            System.out.println("MarkSqlProvider-selectFallGrade-stu_info_id--");
        }
        if (markCondition.getTermYear() != null&&!("".equals(markCondition.getTermYear()))) {
            sql.append(" and term_year=#{termYear}");
            System.out.println("MarkSqlProvider-selectFallGrade-termYear--");
        }
        if (markCondition.getTermNum() != null&&!("".equals(markCondition.getTermNum()))) {
            sql.append(" and term_num=#{termNum}");
            System.out.println("MarkSqlProvider-selectFallGrade-termNum--");
        }
        return sql.toString();
    }

    /**
     * @Description 根据学期id，courseid coursename 查询课程
     * @author 20155884 王晓阳
     * @methodName
     * @param
     * @returnType
     * @create 2018/5/28 8:00
     **/
    public String findCourseTerm(MarkCondition markCondition){
        StringBuffer sql = new StringBuffer("select * from course_term,course,term where 1=1 and course_term.course_id=course.course_id and course_term.term_id=term.term_id ");
       /* StringBuffer sql = new StringBuffer("select course.*,term.*" +
                " from course_term,course,term where 1=1 and course_term.course_id=course.course_id and course_term.term_id=term.term_id ");*/
        //课程号---
        if (markCondition.getCourseId() != null) {
            //  sql.append(" and course_id=#{courseId}");
            sql.append(" and course.course_id=#{courseId}");
            System.out.println("--course_id--"+markCondition.getCourseId());
        }
        //课程名称
        if (markCondition.getCourseName() != null&&!("".equals(markCondition.getCourseName()))) {
            sql.append(" and course.course_name=#{courseName}");
            System.out.println("-MarkSqlProvider-getCourseName--"+markCondition.getCourseName());
        }
        //课程类型
        if (markCondition.getCourseType()!=null&&!("".equals(markCondition.getCourseType()))) {
            sql.append(" and course.course_type=#{courseType}");
            System.out.println("-MarkSqlProvider-course_type666--");
        }
        if (markCondition.getTermYear() != null&&!("".equals(markCondition.getTermYear()))) {
            sql.append(" and term_year=#{termYear}");
            System.out.println("-MarkSqlProvider-termYear--");
        }
        if (markCondition.getTermNum() != null&&!("".equals(markCondition.getTermNum()))) {
            sql.append(" and term_num=#{termNum}");
            System.out.println("-MarkSqlProvider-termNum--");
        }
        return sql.toString();
    }
    /**
     * @Description 查询各分数段的学生人数
     * @author 20155884 王晓阳
     * @methodName
     * @param
     * @returnType
     * @create 2018/6/1 19:21
     **/ 
    public String selectMarkCount(MarkCount markCount){
        StringBuffer sql = new StringBuffer("SELECT c.course_id,c.course_name,t.*, " +
                "COUNT(CASE WHEN m.mark BETWEEN 95 AND 100 THEN 1 END) AS ninety5," +
                "COUNT(CASE WHEN m.mark BETWEEN 90 AND 94 THEN 1 END) AS ninety," +
                "COUNT(CASE WHEN m.mark BETWEEN 85 AND 89 THEN 1 END) AS eighty5," +
                "COUNT(CASE WHEN m.mark BETWEEN 80 AND 84 THEN 1 END) AS eighty," +
                "COUNT(CASE WHEN m.mark BETWEEN 75 AND 79 THEN 1 END) AS seventy5," +
                "COUNT(CASE WHEN m.mark BETWEEN 70 AND 74 THEN 1 END) AS seventy," +
                "COUNT(CASE WHEN m.mark BETWEEN 65 AND 69 THEN 1 END) AS sixty5," +
                "COUNT(CASE WHEN m.mark BETWEEN 60 AND 64 THEN 1 END) AS sixty," +
                "COUNT(CASE WHEN m.mark <60 THEN 1 END) AS fifty " +
                "from course c,mark m,term t WHERE c.course_id=m.course_id AND m.term_id=t.term_id ");

        //课程号---
        if (markCount.getCourseId() != null) {
            sql.append(" and c.course_id=#{courseId}");
            System.out.println("--course_id--"+markCount.getCourseId());
        }
        //课程名称
        if (markCount.getCourseName() != null&&!("".equals(markCount.getCourseName()))) {
            sql.append(" and c.course_name=#{courseName}");
            System.out.println("-MarkSqlProvider-getCourseName--"+markCount.getCourseName());
        }

        if (markCount.getTermYear() != null&&!("".equals(markCount.getTermYear()))) {
            sql.append(" and term_year=#{termYear}");
            System.out.println("-MarkSqlProvider-termYear--");
        }
        if (markCount.getTermNum() != null&&!("".equals(markCount.getTermNum()))) {
            sql.append(" and term_num=#{termNum}");
            System.out.println("-MarkSqlProvider-termNum--");
        }
        return sql.toString();
    }

    /**
     * @param markCondition
     * @Description 查询学生的总学分
     * @author 20155884 王晓阳
     * @methodName findStuCredithour
     * @returnType Msg
     * @create 2018/5/10 11:48
     **/
//COALESCE(SUM(c.course_credithour),0)
    public String findStuCredithour1(MarkCondition markCondition){
        StringBuffer sql = new StringBuffer("select m.*,SUM(c.course_credithour) AS stuCourseCresithour,c.*,t.*,tm.*,s.stu_name,s.stu_num " +
                "from mark m,course c,course_term t ,student s,term tm " +
                "where 1=1 " +
                "and m.course_id=c.course_id " +
                "and t.course_id=c.course_id " +
                "and m.mark>=60 " +
                "AND m.term_id=t.term_id " +
                "AND tm.term_id=t.term_id " +
                "and m.stu_info_id=s.stu_info_id ");
        //学生id
        if (markCondition.getStuInfoId()!= null) {
            sql.append(" and m.stu_info_id=#{stuInfoId}  ");
            System.out.println("--stu_info_id--");
        }
        //学期id
        if (markCondition.getTermId() != null) {
            sql.append(" and m.term_id=#{termId}");
            System.out.println("--termId--");
        }
        return sql.toString();
    }
    /**
     * @param markCondition
     * @Description 根据学生id查询不及格成绩的个数
     * @author 20155884 王晓阳
     * @methodName findFailGradeCount
     * @returnType Msg
     * @create 2018/5/10 11:48
     **/
    public String findFailGradeCount(MarkCondition markCondition){
        StringBuffer sql = new StringBuffer("select count(mark_id) from mark,course where 1=1 and mark.course_id=course.course_id and mark.mark<60 ");
        //课程号---
        if (markCondition.getCourseId() != null) {
            //  sql.append(" and course_id=#{courseId}");
            sql.append(" and mark.course_id=#{courseId}");
            System.out.println("--course_id--"+markCondition.getCourseId());
        }
        //课程名称
        if (markCondition.getCourseName() != null&&!("".equals(markCondition.getCourseName()))) {
            sql.append(" and course.course_name=#{courseName}");
            System.out.println("-MarkSqlProvider-getCourseName--"+markCondition.getCourseName());
        }
        //课程类型
        if (markCondition.getCourseType()!=null&&!("".equals(markCondition.getCourseType()))) {
            sql.append(" and course.course_type=#{courseType}");
            System.out.println("-MarkSqlProvider-course_type666--");
        }
        //学生id
        if (markCondition.getStuInfoId()!= null) {
            sql.append(" and mark.stu_info_id=#{stuInfoId}  ");
            System.out.println("--stu_info_id--");
        }
        //学期id
        if (markCondition.getTermId() != null) {
            sql.append(" and term_id=#{termId}");
            System.out.println("--termId--");
        }
        return sql.toString();
    }

}
